﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HolterDatabase.Model
{
    public class HolterEntry
    {
        public HolterEntry() 
        {
        }

        public HolterEntry(System.Data.DataRow dataRow)
        {
            HolterId = (int)(dataRow["Id"]);
            PatientId = (int)(dataRow["PatientId"]);
            FirstName = dataRow["FirstName"] as string;
            LastName = dataRow["LastName"] as string;
            BirthDate = dataRow["BirthDate"] as DateTime?;
            Adress = dataRow["Adress"] as string;
            Phone = dataRow["Phone"] as string;
            OutDate = dataRow["OutDate"] as DateTime?;
            OutTime = dataRow["OutTime"] as System.TimeSpan?;
            PlayDate = dataRow["PlayDate"] as System.DateTime?;
            Indication = dataRow["Indication"] as string;
            Treatment = dataRow["Treatment"] as string;
            Doctor = dataRow["Doctor"] as string;
            Comment = dataRow["Comment"] as string;
            Conclusion = dataRow["Conclusion"] as string;
            Payment = (dataRow["Payment"] is System.DBNull) ? false : (bool)(dataRow["Payment"]);
            Validated = (dataRow["Validated"] is System.DBNull) ? false : (bool)(dataRow["Validated"]);
            Duration = (dataRow["Duration"] is System.DBNull) ? 0 : (int)(dataRow["Duration"]);
            ReturnDate = dataRow["ReturnDate"] as DateTime?;
            ExamNumber = (dataRow["ExamNumber"] is System.DBNull) ? 0 : (int)(dataRow["ExamNumber"]);
            Internal = (dataRow["Internal"] is System.DBNull) ? false : (bool)(dataRow["Internal"]);
        }

        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime? BirthDate { get; set; }
        public string Adress { get; set; }
        public string Phone { get; set; }
        public DateTime? OutDate { get; set; }
        public System.TimeSpan? OutTime { get; set; }
        public DateTime? PlayDate { get; set; }
        public string Indication { get; set; }
        public string Treatment { get; set; }
        public string Comment { get; set; }
        public string Conclusion { get; set; }
        public string Doctor { get; set; }
        public int HolterId { get; set; }
        public int PatientId { get; set; }
        public bool Payment { get; set; }
        public bool Validated { get; set; }
        public int Duration { get; set; }
        public DateTime? ReturnDate { get; set; }
        public int ExamNumber { get; set; }
        public bool Internal { get; set; }

        public void Insert()
        {
            using (MySqlConnection connection = new MySqlConnection(DatabaseSet.connectionString))
            {
                // Insert Command
                string insertString = "BEGIN;" +
                                      "INSERT INTO patient (FirstName, LastName, BirthDate, Adress, Phone) VALUES (?FirstName, ?LastName, ?BirthDate, ?Adress, ?Phone);" +
                                      "INSERT INTO holter (PatientId, OutDate, PlayDate, OutTime, Indication, Treatment, Doctor, Comment, Conclusion, Payment, Validated, Duration, ReturnDate, ExamNumber, Internal) VALUES (LAST_INSERT_ID(), ?OutDate, ?PlayDate, ?OutTime, ?Indication, ?Treatment, ?Doctor, ?Comment, ?Conclusion, ?Payment, ?Validated, ?Duration, ?ReturnDate, ?ExamNumber, ?Internal);" +
                                      "COMMIT";
                MySqlCommand insertCommand = new MySqlCommand(insertString, connection);
                insertCommand.Parameters.AddWithValue("?FirstName", FirstName);
                insertCommand.Parameters.AddWithValue("?LastName", LastName);
                insertCommand.Parameters.AddWithValue("?BirthDate", BirthDate);
                insertCommand.Parameters.AddWithValue("?Adress", Adress);
                insertCommand.Parameters.AddWithValue("?Phone", Phone);
                insertCommand.Parameters.AddWithValue("?OutDate", OutDate);
                insertCommand.Parameters.AddWithValue("?OutTime", OutTime);
                insertCommand.Parameters.AddWithValue("?PlayDate", PlayDate);
                insertCommand.Parameters.AddWithValue("?Indication", Indication);
                insertCommand.Parameters.AddWithValue("?Treatment", Treatment);
                insertCommand.Parameters.AddWithValue("?Doctor", Doctor);
                insertCommand.Parameters.AddWithValue("?Comment", Comment);
                insertCommand.Parameters.AddWithValue("?Conclusion", Conclusion);
                insertCommand.Parameters.AddWithValue("?Payment", Payment);
                insertCommand.Parameters.AddWithValue("?Validated", Validated);
                insertCommand.Parameters.AddWithValue("?Duration", Duration);
                insertCommand.Parameters.AddWithValue("?ReturnDate", ReturnDate);
                insertCommand.Parameters.AddWithValue("?ExamNumber", ExamNumber);
                insertCommand.Parameters.AddWithValue("?Internal", Internal);

                connection.Open();
                insertCommand.ExecuteNonQuery();
                connection.Close();
            }
        }

        public void Update()
        {
            using (MySqlConnection connection = new MySqlConnection(DatabaseSet.connectionString))
            {
                // Update command
                string updateString = "BEGIN;" +
                                      "UPDATE patient SET patient.FirstName=?FirstName, patient.LastName=?LastName, patient.BirthDate=?BirthDate, patient.Adress=?Adress, patient.Phone=?Phone WHERE patient.Id=?Id;" +
                                      "UPDATE holter SET holter.PatientId=?Id, holter.OutDate=?OutDate, holter.OutTime=?OutTime, holter.PlayDate=?PlayDate, holter.Indication=?Indication, holter.Treatment=?Treatment, holter.Doctor=?Doctor, holter.Comment=?Comment, holter.Conclusion=?Conclusion, holter.Payment=?Payment, holter.Validated=?Validated, holter.Duration=?Duration, holter.ReturnDate=?ReturnDate, holter.ExamNumber=?ExamNumber, holter.Internal=?Internal WHERE holter.Id=?IdHolter;" +
                                      "COMMIT";
                MySqlCommand updateCommand = new MySqlCommand(updateString, connection);
                updateCommand.Parameters.AddWithValue("?FirstName", FirstName);
                updateCommand.Parameters.AddWithValue("?LastName", LastName);
                updateCommand.Parameters.AddWithValue("?BirthDate", BirthDate);
                updateCommand.Parameters.AddWithValue("?Adress", Adress);
                updateCommand.Parameters.AddWithValue("?Phone", Phone);
                updateCommand.Parameters.AddWithValue("?OutDate", OutDate);
                updateCommand.Parameters.AddWithValue("?OutTime", OutTime);
                updateCommand.Parameters.AddWithValue("?PlayDate", PlayDate);
                updateCommand.Parameters.AddWithValue("?Indication", Indication);
                updateCommand.Parameters.AddWithValue("?Treatment", Treatment);
                updateCommand.Parameters.AddWithValue("?Doctor", Doctor);
                updateCommand.Parameters.AddWithValue("?Id", PatientId);
                updateCommand.Parameters.AddWithValue("?IdHolter", HolterId);
                updateCommand.Parameters.AddWithValue("?Comment", Comment);
                updateCommand.Parameters.AddWithValue("?Conclusion", Conclusion);
                updateCommand.Parameters.AddWithValue("?Payment", Payment);
                updateCommand.Parameters.AddWithValue("?Validated", Validated);
                updateCommand.Parameters.AddWithValue("?Duration", Duration);
                updateCommand.Parameters.AddWithValue("?ReturnDate", ReturnDate);
                updateCommand.Parameters.AddWithValue("?ExamNumber", ExamNumber);
                updateCommand.Parameters.AddWithValue("?Internal", Internal);

                connection.Open();
                updateCommand.ExecuteNonQuery();
                connection.Close();
            }
        }
    }
}
